import threading
import sqlite3
import time
import os
import sys
import cPickle as pickle

from Queue import Queue

DB_DIR = './'
DB_FILENAME =  'data.db'
DB_DEBUG = True


dbs = {}

def get_database(dir, filename):
    global dbs
    name = dir + filename
    if name in dbs:
        db = dbs[name]
    else:
        db = Database(dir, filename)
        dbs[name] = db
    return db

class Database:

    class ConnWrapper:
    
        def __init__(self, file):
            self.__file = file
   
        def __dict_factory(self, cursor, row):
            d = {}
            for idx, col in enumerate(cursor.description):
                d[col[0]] = row[idx]
            return d

        def execute(self, query, arg=None):
            conn = sqlite3.connect(self.__file)
            conn.row_factory = self.__dict_factory
            dres = []
            try:
                c = conn.cursor()
                res = c.execute(query, arg or tuple())
                for r in res:
                    dres.append(r)
                conn.commit()
            finally:
                conn.close()
            return dres
                
    def __init__(self, d=DB_DIR, f=DB_FILENAME):
        DB_PATH = d + '/' + f
        if not os.path.exists(d):
            os.makedirs(d)

        if not os.path.exists(DB_PATH):
            create = True
        else:
            create = False
        self.__conn = Database.ConnWrapper(DB_PATH)
        if create:
            self.__create()
       
        self.__tables = {}


        for row in self.__conn.execute(''' SELECT pickled FROM tables '''):
            pickled = row['pickled']
            if pickled == 'tables':
                continue
            try:
                table = pickle.loads(pickled.encode('ascii'))
                table.set_conn(self.__conn)
                self.__tables[table.__class__.__name__] = table
            except Exception, e:
                print "Couldn't load table:",e

        print 'Finished init. Tables:',self.__tables
   
    def __create(self):
        print 'creating tables'
        self.__conn.execute(''' CREATE TABLE tables (id INTEGER PRIMARY KEY, pickled STRING) ''')
        self.__conn.execute(''' INSERT INTO tables VALUES (?, ?) ''', (1, 'tables'))
        print 'created tables'
   
    def clear(self):
        self.__conn.close()
        os.remove(DB_FILE_URL)
        self.__conn = Database.ThreadedConnection(DB_FILE_URL)
        self.__create()
       
    def reset_tables(self):
        for table in self.__tables.values():
            table.reset()
       
    def add_table(self, table):
        #full_name = table.__module__ + table.__class__.__name__
        #if full_name.startswith('__main__'):
        #    full_name = full_name[8:]
        pickled = pickle.dumps(table)
        [result] = self.__conn.execute(''' SELECT COUNT(*) AS count FROM tables WHERE pickled=? ''', (pickled,))
        if result['count'] > 0:
            raise RuntimeError('There is already a table called ' + table.__class__.__name__)
        else:
            self.__conn.execute(''' INSERT INTO tables VALUES (?,?) ''', (None,pickled))
            table.set_conn(self.__conn)
            table._init()
            self.__tables[table.__class__.__name__] = table
   
    def get_table(self, cls):
        name = cls.__name__
        if name in self.__tables:
            return self.__tables[name]
        else:
            raise RuntimeError("That table doesn't exist.")
    
    def has_table(self, cls):
        return cls.__name__ in self.__tables

    def get_or_create(self, cls):
        #full_name = cls.__module__ + cls.__name__
        #if full_name.startswith('__main__'):
        #    full_name = full_name[8:]
        if self.has_table(cls):
            return self.get_table(cls)
        else:
            instance = cls()
            self.add_table(instance)
            return instance

    def shutdown(self):
        pass
            
       
class Table:

    def __init__(self):
        pass

    def set_conn(self, conn):
        self._conn = conn
       
    def reset(self):
        self._drop()
        self._init()
       
    def _drop(self):
        raise NotImplementedError
   
    def _init(self):
        raise NotImplementedError
       
class MessageMeta(Table):
   
    def __init__(self):
        Table.__init__(self)

    def _init(self):
        self._conn.execute('''
            create table message_meta
            (mid INTEGER PRIMARY KEY NOT NULL, expires INTEGER NOT NULL, delivered INTEGER)
            ''')
           
    def _drop(self):
        self._conn.execute('''
            drop table message_meta
            ''')
       
    def reset(self, conn):
        self._drop()
        self._init()
       
    def register(self, msg):
        c = self._conn
        c.execute(''' INSERT INTO message_meta VALUES (?,?,?) ''', (msg.mid, msg.expires, msg.delivered))
       
    def is_expired(self, mid):
        c = self._conn
        r = c.execute(''' SELECT expires FROM message_meta WHERE mid=? ''', (mid,))
        if r is None:
            raise 'Message ' + str(mid) + ' not in DB.'
        return r[0] < time.time()
           
    def is_delivered(self, mid):
        c = self._conn
        r = c.execute(''' SELECT delivered FROM message_meta WHERE mid=? ''', (mid,))
        if r is None:
            raise 'Message ' + str(mid) + ' not in DB.'
        return r[0] is not None

    def get_all(self):
        c = self._conn
        rows = c.execute(''' SELECT * FROM message_meta ''')
        msgs = []
        for r in rows:
            msgs.append({'mid':r[0], 'expires':r[1], 'delivered':r[2]})
        return msgs
    
           
   
if __name__ == '__main__':
    class Message:
        def __init__(self, mid, expires, delivered):
            self.mid = mid
            self.expires = expires
            self.delivered = delivered
    d = Database()
    
    try:       
       
        
        #if d.has_table('MessageMeta'):
        #    m = d.get_table('MessageMeta')
        #else:
        #    m = MessageMeta()
        #    d.add_table(m)

        m = d.get_or_create(MessageMeta)

        #m.register(Message(12345, 1, 2))
        #m.register(Message(45678, 5, 6))
        print 'delivered?',m.is_delivered(12345)
        print 'expired?',m.is_expired(12345)
        print 'all:',m.get_all()
        #print 'should die:',m.is_delivered(123123123)
        #assert False # kept running!
    finally:
        d.shutdown()
